64.mysql优化之数据查询优化 |
您所在的位置：网站首页 › 多表left join太慢 › 64.mysql优化之数据查询优化 |
64.mysql优化之数据查询优化
|
字段过长如何优化
比如现在有一个article表,content字段非常大。 select content from article where content='很长的一段字符串'; 复制代码如果对content字段加索引,也会很慢。 此时可以增加一个字段content_crc,对content字段使用哈希生成索引存在content_article。 假设针对这个很长的一段字符串生成的索引是7x58c23x55。 select content from article where content='很长的一段字符串' and content_crc='7x58c23x55'; 复制代码这样即使有多个哈希值相同的索引值,查找仍然很快！这样的缺陷是需要维护哈希值。 优化limit分页在进行分页时，一般通过创建覆盖索引，能够比较好的提高性能。一个非常常见，而又非常头疼的分页场景就是 "limit 1000000,10" ，此时MySQL需要搜索出前1000010 条记录后，仅仅需要返回第 1000001 到 1000010 条记录，前1000000 记录会被抛弃，查询代价非常大。 当点击比较靠后的页码时，就会出现这个问题，查询效率非常慢。 优化SQL： select * from operation_log limit 3000000 , 10; 复制代码将上述SQL优化为 : select * from operation_log t , (select id from operation_log order by id limit 3000000,10) b where t.id = b.id ; 复制代码 select id , operate_class as operateClass , operate_method as operateMethod, return_class as returnClass, operate_user as operateUser, operate_time as operateTime, param_and_value as paramAndValue, cost_time as costTime, return_value as returnValue from operation_log t, (select id from operation_log order by id limit #{start},#{rows}) b where t.id = b.id 复制代码 优先用Inner join，如果是left join，左边表尽量小 Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集。 left join 在两张表进行连接查询时，会返回左表所有的行。 right join 在两张表进行连接查询时，会返回右表所有的行。 在where及orderBy的列建索引,避免全表扫描 连续的数值，能用 between 就不要用 in对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值。 exist & in的合理利用 外表小,内表大,EXISTS 外表大,内表小,IN 优化group by 语句: 优化:不需要排序:order by null 子查询临时表太慢MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。 子查询虽然很灵活，但是执行效率并不高。 执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。 explain select * from t_user where id in (select user_id from user_role ); 复制代码优化： 可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。 优化:使用join代替子查询 explain select * from t_user u , user_role ur where u.id = ur.user_id; 复制代码连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成。 这个逻辑上需要两个步骤的查询工作。 优化深度分页查询 select * from tb_item t limit 2000000,10 复制代码一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。 优化思路一在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容 select * from tb_item t ,(select id from tb_item order by id limit 2000000,10) as a where a.id = t.id 复制代码 优化思路二该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。 select * from tb_item t where id> 2000000 limit 10 复制代码 强制使用索引&忽略索引 引导使用索引:USE INDEXSQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。 在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。 SELECT * FROM single_table s1 USE INDEX ( idx_key1 ) WHERE s1.common_field = 'a'; 复制代码 强制使用索引:FORCE INDEX SELECT * FROM single_table s1 FORCE INDEX ( idx_key1 ) WHERE s1.common_field = 'a'; 复制代码 强制忽略索引:IGNORE INDEX SELECT * FROM single_table s1 IGNORE INDEX ( idx_key1 ) WHERE s1.common_field = 'a'; 复制代码 COUNT 优化 数据库计数 Redis缓存count(字段) |
【本文地址】
今日新闻 |
推荐新闻 |